package com.yellowpage.bigdata.service;

import java.util.List;
import java.util.Map;

import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.yellowpage.white.common.Constants;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;
import com.yellowpage.bigdata.data.NspOnofflogImeiMonthregionSum;
import com.yellowpage.bigdata.dao.NspOnofflogImeiMonthregionSumDao;

public class NspOnofflogImeiMonthregionSumServiceImpl extends BaseServiceImpl implements NspOnofflogImeiMonthregionSumService {
	  private NspOnofflogImeiMonthregionSumDao nspOnofflogImeiMonthregionSumDao;
	
	  public void setNspOnofflogImeiMonthregionSumDao(NspOnofflogImeiMonthregionSumDao nspOnofflogImeiMonthregionSumDao) {
		  this.nspOnofflogImeiMonthregionSumDao = nspOnofflogImeiMonthregionSumDao;
	  }
	
	
	  /* Smooth generate Method */ 
	  public NspOnofflogImeiMonthregionSum getNspOnofflogImeiMonthregionSum(Long id){
	      return nspOnofflogImeiMonthregionSumDao.get(id);
	  }
	  
    public List<NspOnofflogImeiMonthregionSum> getAllNspOnofflogImeiMonthregionSum(){
        return nspOnofflogImeiMonthregionSumDao.getAll();
    }
    
    public List<Map> queryNspOnofflogImeiMonthregionSumByPage(Map queryMap,
			PageRender pageRender) {
    	String t2 = queryMap.get("regionCode").equals("") ? "" : " and REGION_CODE in "+queryMap.get("regionCode");
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.REGION_CODE , " +
						"(SELECT c.NAME FROM OMC_REGION c WHERE c.REGION_CODE = a.REGION_CODE) as REGION_NAME," +
						"a.LOG_MONTH , " +
						"a.CREATE_USER , a.CREATE_TIME , " +
						"a.NOTE , " +
						"(select IMEI_ALL_COUNT from NSP_ONOFFLOG_IMEI_MONTHREGION_SUM c where c.LOG_MONTH=a.LOG_MONTH AND c.OPT_TYPE='A' and c.REGION_CODE=a.REGION_CODE) as IMEI_ALL_COUNT," +
						"(select IMEI_MONTH_COUNT from NSP_ONOFFLOG_IMEI_MONTHREGION_SUM c where c.LOG_MONTH=a.LOG_MONTH AND c.OPT_TYPE='3' and c.REGION_CODE=a.REGION_CODE) as LXNUM," +
						"(select IMEI_MONTH_COUNT from NSP_ONOFFLOG_IMEI_MONTHREGION_SUM c where c.LOG_MONTH=a.LOG_MONTH AND c.OPT_TYPE='Z' and c.REGION_CODE=a.REGION_CODE) as ZDNUM," +
						"(select IMEI_MONTH_COUNT from NSP_ONOFFLOG_IMEI_MONTHREGION_SUM c where c.LOG_MONTH=a.LOG_MONTH AND c.OPT_TYPE='A' and c.REGION_CODE=a.REGION_CODE) as IMEI_MONTH_COUNT," +
						"(select IMEI_NEW_COUNT from NSP_ONOFFLOG_IMEI_MONTHREGION_SUM c where c.LOG_MONTH=a.LOG_MONTH AND c.OPT_TYPE='A' and c.REGION_CODE=a.REGION_CODE) as IMEI_NEW_COUNT, " +
						"a.LAST_UPDATE_USER , a.LAST_UPDATE_TIME , a.ONOFFLOG_IMEI_MONTHREGION_SUM_ID    " +
						"FROM NSP_ONOFFLOG_IMEI_MONTHREGION_SUM a WHERE 1=1 "+t2)
//				.addCondition(" and REGION_CODE like :regionCode ")
				.addCondition(" and LOG_MONTH >= :query_tm_start ")
				.addCondition(" and LOG_MONTH <= :query_tm_end ");
		sqlAssistant.setTail(" GROUP BY a.REGION_CODE");
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}
	
	public List<Map> queryNspOnofflogImeiMonthregionSumByPage(Map queryMap) {
		String t2 = queryMap.get("regionCode").equals("") ? "" : " and REGION_CODE in "+queryMap.get("regionCode");
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.REGION_CODE , " +
						"(SELECT c.NAME FROM OMC_REGION c WHERE c.REGION_CODE = a.REGION_CODE) as REGION_NAME," +
						"a.LOG_MONTH , " +
						"a.CREATE_USER , a.CREATE_TIME , " +
						"a.NOTE , " +
						"(select IMEI_ALL_COUNT from NSP_ONOFFLOG_IMEI_MONTHREGION_SUM c where c.LOG_MONTH=a.LOG_MONTH AND c.OPT_TYPE='A' and c.REGION_CODE=a.REGION_CODE) as IMEI_ALL_COUNT," +
						"(select IMEI_MONTH_COUNT from NSP_ONOFFLOG_IMEI_MONTHREGION_SUM c where c.LOG_MONTH=a.LOG_MONTH AND c.OPT_TYPE='3' and c.REGION_CODE=a.REGION_CODE) as LXNUM," +
						"(select IMEI_MONTH_COUNT from NSP_ONOFFLOG_IMEI_MONTHREGION_SUM c where c.LOG_MONTH=a.LOG_MONTH AND c.OPT_TYPE='Z' and c.REGION_CODE=a.REGION_CODE) as ZDNUM," +
						"(select IMEI_MONTH_COUNT from NSP_ONOFFLOG_IMEI_MONTHREGION_SUM c where c.LOG_MONTH=a.LOG_MONTH AND c.OPT_TYPE='A' and c.REGION_CODE=a.REGION_CODE) as IMEI_MONTH_COUNT," +
						"(select IMEI_NEW_COUNT from NSP_ONOFFLOG_IMEI_MONTHREGION_SUM c where c.LOG_MONTH=a.LOG_MONTH AND c.OPT_TYPE='A' and c.REGION_CODE=a.REGION_CODE) as IMEI_NEW_COUNT, " +
						"a.LAST_UPDATE_USER , a.LAST_UPDATE_TIME , a.ONOFFLOG_IMEI_MONTHREGION_SUM_ID    " +
						"FROM NSP_ONOFFLOG_IMEI_MONTHREGION_SUM a WHERE 1=1 " + t2)
				//.addCondition(" and REGION_CODE like :regionCode ")
				.addCondition(" and LOG_MONTH >= :query_tm_start ")
				.addCondition(" and LOG_MONTH <= :query_tm_end ");
		sqlAssistant.setTail(" GROUP BY a.REGION_CODE");
		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);

		return resultList;
	}
    
    
    public void saveNspOnofflogImeiMonthregionSum(NspOnofflogImeiMonthregionSum nspOnofflogImeiMonthregionSum){
        nspOnofflogImeiMonthregionSumDao.save(nspOnofflogImeiMonthregionSum);
    }
    
	  public void updateNspOnofflogImeiMonthregionSum(NspOnofflogImeiMonthregionSum nspOnofflogImeiMonthregionSum){
	      nspOnofflogImeiMonthregionSumDao.update(nspOnofflogImeiMonthregionSum);
	  }
	  
	  public void delete(List deleteList){
	      nspOnofflogImeiMonthregionSumDao.remove(deleteList);
	  }
	  
	  
	 
	
}	